---------------------------------------------------------------------------
-- Create set of tables with PK, FK, UQ, CC and ADT.sql
---------------------------------------------------------------------------
-- drop all tables
-------------------------
if exists (select *
             from Information_Schema.Tables
            where Table_Schema = 'Dbo' and
                  Table_Type = 'Base Table' and
                  Table_Name = 'tblTestEmployee' )
  drop table dbo.tblTestEmployee


if exists (select *
             from Information_Schema.Tables
            where Table_Schema = 'Dbo' and
                  Table_Type = 'Base Table' and
                  Table_Name = 'tblContractor' )
  drop table dbo.tblContractor


if exists (select *
             from Information_Schema.Tables
            where Table_Schema = 'Dbo' and
                  Table_Type = 'Base Table' and
                  Table_Name = 'tblPayroll' )
  drop table dbo.tblPayroll




if exists (select *
             from Information_Schema.Tables
            where Table_Schema = 'Dbo' and
                  Table_Type = 'Base Table' and
                  Table_Name = 'tblBonus' )
  drop table dbo.tblBonus


if exists (select *
             from Information_Schema.Tables
            where Table_Schema = 'Dbo' and
                  Table_Type = 'Base Table' and
                  Table_Name = 'tblDepartment' )
  drop table dbo.tblDepartment


if exists (select *
             from Information_Schema.Tables
            where Table_Schema = 'Dbo' and
                  Table_Type = 'Base Table' and
                  Table_Name = 'tblSaleDate' )
  drop table dbo.tblSaleDate


-------------------------
-- create adt
-------------------------
if exists( select * from systypes where [name] = 'adt_IntId')
     exec sp_droptype 'adt_IntId'
exec sp_addtype
@typename = 'adt_IntId'
,@phystype = 'int'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'


if exists( select * from systypes where [name] = 'adt_SmallDateTimeId')
     exec sp_droptype 'adt_SmallDateTimeId'
exec sp_addtype
@typename = 'adt_SmallDateTimeId'
,@phystype = 'smalldatetime'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'



-------------------------------------------------
-- create tables with PK, FK, UQ, CC and ADT
-------------------------------------------------

create table dbo.tblSaleDate (
         SaleId            int          NOT NULL
        ,StoreId           int          NOT NULL
        ,CreatedDate       datetime     NOT NULL
)

alter table dbo.tblSaleDate
alter column CreatedDate adt_SmallDateTimeId

alter table dbo.tblSaleDate
add constraint PK_tblSaleDate_CreatedDate
primary key clustered (CreatedDate) with Fillfactor = 96



create table dbo.tblDepartment (
         DepartmentId      int NOT NULL
constraint CC_tblDepartment_DepartmentId CHECK (DepartmentId between 6789 
and 9567)
        ,DepartmentName    varchar(100) NOT NULL
        ,CreatedDate       datetime     NOT NULL
)

alter table dbo.tblDepartment
alter column DepartmentId adt_IntId

alter table dbo.tblDepartment
alter column CreatedDate adt_SmallDateTimeId

alter table dbo.tblDepartment
add constraint CC_Department_CreatedDate CHECK (CreatedDate >= getdate())



alter table dbo.tblDepartment
add constraint PK_tblDepartment_DepId
primary key clustered (DepartmentId) with Fillfactor = 97


alter table dbo.tblDepartment
add constraint FK_tblDepartment_CreatedDate FOREIGN KEY (CreatedDate)
REFERENCES dbo.tblSaleDate (CreatedDate)
on delete no action
on update cascade



create table dbo.tblTestEmployee (
         EmployeeId        int          NOT NULL
        ,EmployeeFirstName varchar(100) NOT NULL
        ,DepartmentId      decimal(8,3) NOT NULL
        ,CreatedDate       datetime     NOT NULL

)

alter table dbo.tblTestEmployee
alter column EmployeeId adt_IntId

alter table dbo.tblTestEmployee
alter column DepartmentId adt_IntId


alter table dbo.tblTestEmployee
add constraint PK_tblTestEmployee_EmployeeId
primary key clustered (EmployeeId, EmployeeFirstName, DepartmentId) with 
Fillfactor = 94




alter table dbo.tblTestEmployee
alter column CreatedDate adt_SmallDateTimeId

alter table dbo.tblTestEmployee
add constraint CC_TestEmployee_DepartmentId CHECK (DepartmentId between 6789 
and 9567)

alter table dbo.tblTestEmployee
add constraint CC_TestEmployee_CreatedDate CHECK (CreatedDate >= getdate())

alter table dbo.tblTestEmployee
add constraint FK_tblTestEmployee_DeptId FOREIGN KEY (DepartmentId)
REFERENCES dbo.tblDepartment (DepartmentId)
on delete no action
on update cascade

alter table dbo.tblTestEmployee
add constraint FK_tblTestEmployee_CreatedDate FOREIGN KEY (CreatedDate)
REFERENCES dbo.tblSaleDate (CreatedDate)
on delete no action
on update no action



create table dbo.tblContractor (
         ContractId        int          NOT NULL
        ,EmployeeId        bigint       NOT NULL
        ,DepartmentId      decimal(8,3) NOT NULL
        ,EmployeeFirstName varchar(100) NOT NULL
        ,CreatedDate       datetime     NOT NULL
        ,ContractLocation  int          NOT NULL
)

alter table dbo.tblContractor
alter column ContractId adt_IntId

alter table dbo.tblContractor
alter column EmployeeId adt_IntId

alter table dbo.tblContractor
alter column DepartmentId adt_IntId

alter table dbo.tblContractor
alter column CreatedDate adt_SmallDateTimeId


alter table dbo.tblContractor
add constraint PK_tblContractor_ContractId
primary key clustered (ContractId, EmployeeId, DepartmentId) with Fillfactor 
= 94

alter table dbo.tblContractor
add constraint UQ_tblContractor_EmpId Unique nonclustered (EmployeeId)

alter table dbo.tblContractor
add constraint UQ_tblContractor_DeptId_ContractLocation Unique nonclustered 
(DepartmentId, ContractLocation)

alter table dbo.tblContractor
add constraint UQ_tblContractor_DeptId_CreatedDate Unique nonclustered 
(DepartmentId, CreatedDate)

alter table dbo.tblContractor
add constraint UQ_tblContractor_CreatedDate Unique nonclustered 
(CreatedDate)


alter table dbo.tblContractor
add constraint FK_tblContractor_CreatedDate FOREIGN KEY (CreatedDate)
REFERENCES dbo.tblSaleDate (CreatedDate)
on delete no action
on update cascade


create table dbo.tblPayroll (
         EmployeeId        int          NOT NULL
        ,EmployeeFirstName varchar(100) NOT NULL
        ,DepartmentId      decimal(8,3) NOT NULL
        ,CreatedDate       datetime     NOT NULL
)
alter table dbo.tblPayroll
alter column EmployeeId adt_IntId

alter table dbo.tblPayroll
add constraint PK_tblPayroll_EmployeeId
primary key clustered (EmployeeId) with Fillfactor = 94

alter table dbo.tblPayroll
alter column DepartmentId adt_IntId

alter table dbo.tblPayroll
alter column CreatedDate adt_SmallDateTimeId

alter table dbo.tblPayroll
add  constraint CC_tblPayroll_CreatedDate CHECK (CreatedDate >= getdate())


alter table dbo.tblPayroll
add constraint FK_tblPayroll_DeptId FOREIGN KEY (DepartmentId)
REFERENCES dbo.tblDepartment (DepartmentId)
on delete no action
on update cascade


alter table dbo.tblPayroll
add constraint FK_tblPayroll_CreatedDate FOREIGN KEY (CreatedDate)
REFERENCES dbo.tblSaleDate (CreatedDate)
on delete no action
on update no action



create table dbo.tblBonus (
         BonusId           int          NOT NULL
        ,EmployeeId        bigint       NOT NULL
        ,EmployeeFirstName varchar(100) NOT NULL
        ,DepartmentId      decimal(8,3) NOT NULL
        ,AppraisalCode     char(9)      NOT NULL
CONSTRAINT CC_tblBonus_AppraisalCode CHECK (AppraisalCode LIKE
         '[A-Z][A-Z][1-9][0-9][0-9][1-9][2-9][3-9][4-9]' and
         AppraisalCode > 'NN3456789')
        ,Country           varchar(100) NOT NULL
        ,State             char(2)          NULL
        ,CreatedDate       datetime     NOT NULL
)


alter table dbo.tblBonus
alter column BonusId adt_IntId

alter table dbo.tblBonus
alter column EmployeeId adt_IntId

alter table dbo.tblBonus
alter column DepartmentId adt_IntId

alter table dbo.tblBonus
add constraint UQ_tblBonus_BonusId_EmpId_DeptId Unique clustered (BonusId, 
EmployeeId, DepartmentId)


alter table dbo.tblBonus
alter column CreatedDate adt_SmallDateTimeId

alter table dbo.tblBonus
add constraint CC_tblBonus_CreatedDate CHECK (CreatedDate >= getdate())


alter table dbo.tblBonus
add CONSTRAINT CC_ForEmployee_Country CHECK ( (Country = 'USA'  and State is 
not null) or
                                  (Country <> 'USA' and State is     null) )


alter table dbo.tblBonus
add constraint FK_tblBonus_CreatedDate FOREIGN KEY (CreatedDate)
REFERENCES dbo.tblSaleDate (CreatedDate)
on delete no action
on update cascade


alter table dbo.tblBonus
add constraint UQ_tblBonus_CreatedDate Unique nonclustered (CreatedDate)